package com.gxg.dao;

import com.gxg.entities.ExperimentalReport;
import java.io.PrintStream;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;

@Repository
public class ExperimentalReportDao
{
  @Autowired
  private JdbcTemplate jdbcTemplate;
  
  public void insertExperimentalReport(ExperimentalReport experimentalReport)
  {
    String sql = "insert into ExperimentalReport values(?,?,?,?,?,?,?,?)";
    this.jdbcTemplate.update(sql, new Object[] {experimentalReport
      .getId(), experimentalReport
      .getTitle(), experimentalReport
      .getName(), experimentalReport
      .getCreateTime(), experimentalReport
      .getExperimentalId(), experimentalReport
      .getExperimentalScore(), experimentalReport
      .getStudentName(), experimentalReport
      .getStudentId() });
  }
  
  public int getExperimentalReportCountByExperimentalId(String experimentalId)
  {
    String sql = "select count(*) from ExperimentalReport where experimentalId=?";
    int rowCount = ((Integer)this.jdbcTemplate.queryForObject(sql, Integer.class, new Object[] { experimentalId })).intValue();
    return rowCount;
  }
  
  public List<ExperimentalReport> getExperimentalReportByExperimentalIdAndStartLimitAndEndLimit(String experimentalId, int limitStart, int limitEnd)
  {
    String sql = "select * from ExperimentalReport where experimentalId=? order by createTime desc limit ?, ?";
    List<ExperimentalReport> experimentalReportList = this.jdbcTemplate.query(sql, new RowMapper()
    {
      public ExperimentalReport mapRow(ResultSet resultSet, int i)
        throws SQLException
      {
        ExperimentalReport experimentalReport = new ExperimentalReport();
        experimentalReport.setId(resultSet.getString("id"));
        experimentalReport.setExperimentalId(resultSet.getString("experimentalId"));
        experimentalReport.setCreateTime(resultSet.getTimestamp("createTime"));
        experimentalReport.setName(resultSet.getString("name"));
        experimentalReport.setTitle(resultSet.getString("title"));
        experimentalReport.setStudentId(resultSet.getString("studentId"));
        experimentalReport.setExperimentalScore(resultSet.getNString("experimentalScore"));
        return experimentalReport;
      }
    }, new Object[] { experimentalId, 
    
      Integer.valueOf(limitStart), Integer.valueOf(limitEnd) });
    return experimentalReportList;
  }
  
  public int getExperimentalReportCountById(String id)
  {
    String sql = "select count(*) from ExperimentalReport where id=?";
    int rowCount = ((Integer)this.jdbcTemplate.queryForObject(sql, Integer.class, new Object[] { id })).intValue();
    return rowCount;
  }
  
  public ExperimentalReport getExperimentalReportById(String id)
  {
    String sql = "select * from ExperimentalReport where id=?";
    ExperimentalReport experimentalReport = (ExperimentalReport)this.jdbcTemplate.queryForObject(sql, new RowMapper()
    {
      public ExperimentalReport mapRow(ResultSet resultSet, int i)
        throws SQLException
      {
        ExperimentalReport experimentalReport = new ExperimentalReport();
        experimentalReport.setId(resultSet.getString("id"));
        experimentalReport.setExperimentalId(resultSet.getString("experimentalId"));
        experimentalReport.setCreateTime(resultSet.getTimestamp("createTime"));
        experimentalReport.setName(resultSet.getString("name"));
        experimentalReport.setTitle(resultSet.getString("title"));
        experimentalReport.setStudentId(resultSet.getString("studentId"));
        experimentalReport.setExperimentalScore(resultSet.getNString("experimentalScore"));
        experimentalReport.setStudentName(resultSet.getString("studentName"));
        return experimentalReport;
      }
    }, new Object[] { id });
    
    return experimentalReport;
  }
  
  public void deleteExperimentalReportById(String id)
  {
    String sql = "delete from ExperimentalReport where id=?";
    this.jdbcTemplate.update(sql, new Object[] { id });
  }
  
  public void updateExperimentalReportScoreById(String id, String score)
  {
    String sql = "update ExperimentalReport set experimentalScore=? where id=?";
    this.jdbcTemplate.update(sql, new Object[] { score, id });
    System.out.println("ok");
  }
  
  public List<ExperimentalReport> getExperimentalReportByExperimentalId(String experimentalId)
  {
    String sql = "select * from ExperimentalReport where experimentalId=?";
    List<ExperimentalReport> experimentalReportList = this.jdbcTemplate.query(sql, new RowMapper()
    {
      public ExperimentalReport mapRow(ResultSet resultSet, int i)
        throws SQLException
      {
        ExperimentalReport experimentalReport = new ExperimentalReport();
        experimentalReport.setId(resultSet.getString("id"));
        experimentalReport.setExperimentalId(resultSet.getString("experimentalId"));
        experimentalReport.setCreateTime(resultSet.getTimestamp("createTime"));
        experimentalReport.setName(resultSet.getString("name"));
        experimentalReport.setTitle(resultSet.getString("title"));
        experimentalReport.setStudentId(resultSet.getString("studentId"));
        experimentalReport.setExperimentalScore(resultSet.getNString("experimentalScore"));
        experimentalReport.setStudentName(resultSet.getString("studentName"));
        return experimentalReport;
      }
    }, new Object[] { experimentalId });
    
    return experimentalReportList;
  }
}
